
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.InputStream;

/**
 * Created by DragonSea
 * on 2018-10-12
 */
public class test3 {
    //读取excel表格
    @Test
    public void readExcel() {
        try {
            //获取文件输入流
            InputStream in = new FileInputStream("E:/test1.xls");
            //得到excel工作簿对象
            HSSFWorkbook wb = new HSSFWorkbook(in);
            //得到Excel工作表对象
            HSSFSheet sheetAt = wb.getSheetAt(0);
            //总行数
            int rowLength = sheetAt.getLastRowNum() + 1;
            //获得excel的行
           // Row row = sheetAt.getRow(0);
            //总列数
            //int colLength = row.getLastCellNum();
            //获得工作表个数
            int sheetCount = wb.getNumberOfSheets();
            for(Row row:sheetAt) {
                if ( row.getRowNum() == 1||row.getRowNum()==0) {
                             continue;
                            }
             //读取数据
               String examId= getValue(row.getCell(0));
               String name=getValue(row.getCell(1));
               String subject=getValue(row.getCell(3));
               String grade=getValue(row.getCell(4));
               String score=getValue(row.getCell(6));
               String gradeRank=getValue(row.getCell(7));
               String schoolRank=getValue(row.getCell(8));
               String classRank=getValue(row.getCell(9));

                System.out.println(examId+","+name+","+subject+","+grade+","+score+","+gradeRank+","+schoolRank+","+classRank);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //转换数据格式
    private static String getValue(Cell cell){
         if( cell.getCellType()== CellType.BOOLEAN){
             return String.valueOf(cell.getBooleanCellValue());
         }else if(cell.getCellType()==CellType.NUMERIC){
         return String.valueOf(cell.getNumericCellValue());
        }else{
             return String.valueOf(cell.getStringCellValue());
         }
    }
}
